package classconverstation.cscl.ist.psu.edu.server;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import classconverstation.cscl.ist.psu.edu.client.messengers.Course;
import classconverstation.cscl.ist.psu.edu.client.messengers.User;
import classconverstation.cscl.ist.psu.edu.client.services.LoginService;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;


/*
 * @Honglu Du
 * This class deal with service side processes when user login
 * getUser(String uid), this method looks up the database to see if the user is already in the DB
 * getClasses(String uid), this method get all the classes the user is enrolled in
 */
public class LoginServiceImpl extends RemoteServiceServlet implements LoginService{

	/**
	 * Automatically generated serial id
	 */
	private static final long serialVersionUID = 9008382491724834500L;
	
	//static final String CLASS_CONVERSATION_DB = "class_conversation_db";
	static final String CLASS_CONVERSATION_DB = "classconversation";
	
	public boolean storeUserInSession(User user) {
		HttpServletRequest httpServletRequest = this.getThreadLocalRequest();
		HttpSession session = httpServletRequest.getSession();
		session.setAttribute("user", user);
		return true;
	}

	// Get the user object, if the user is not in the db, return a null
	public User getUser(String uid){
		try {
			Connection conn = DBConnector.getInstance().getConnection();
			String sql = "SELECT * from " + CLASS_CONVERSATION_DB + "." + "user WHERE uid = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, uid);

			ResultSet result = ps.executeQuery();
			if (result.next()) {
				String fname = result.getString("fname");
				String lname = result.getString("lname");
				String pid = result.getString("psuid");
				int utype = result.getInt("utype");
				User user = new User(uid, fname,lname,pid, utype);
				result.close();
				ps.close();
				conn.close();
				return user;
			}
			else{
				return null;
			}
			
			
		}
		catch (SQLException sqle) {
			System.out.println(sqle.getMessage());
			sqle.printStackTrace();
			return null;
		}
	}

	// Get all the courses in ClassConversation system
	public ArrayList<Course> getAllCourses(){
		ArrayList<Course> allCourses = new ArrayList<Course>();
		try {
			Connection conn = DBConnector.getInstance().getConnection();
			String sql = "SELECT * from " + CLASS_CONVERSATION_DB + "." + "class";
			PreparedStatement ps = conn.prepareStatement(sql);

			ResultSet result = ps.executeQuery();
			while (result.next()) {
				int cid = result.getInt("cid");
				String course_name = result.getString("cname");
				String course_description = result.getString("cdescription");
				Course c = new Course(cid, course_name, course_description);
				allCourses.add(c);
			}
			result.close();
			ps.close();
			conn.close();
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}

		return allCourses;
	}

	// Get all the courses the student is enrolled in
	public ArrayList<Course> getEnrolledCourses(String uid){
		ArrayList<Course> enrolledCourses = new ArrayList<Course>();
		try {
			Connection conn = DBConnector.getInstance().getConnection();
			String sql = "SELECT * from " + CLASS_CONVERSATION_DB + "." + "class WHERE cid IN (SELECT class_cid FROM userclass WHERE user_uid = ?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, uid);
			System.out.println("Get Enrolled Class using "+ uid);
			ResultSet result = ps.executeQuery();
			while (result.next()) {
				int cid = result.getInt("cid");
				String course_name = result.getString("cname");
				String course_description = result.getString("cdescription");
				Course c = new Course(cid, course_name, course_description);
				enrolledCourses.add(c);
			}
			result.close();
			ps.close();
			conn.close();
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return enrolledCourses;
	}
	
	public ArrayList<Course> getCoursesForApproval(String uid){
		ArrayList<Course> courses = new ArrayList<Course>();
		try {
			Connection conn = DBConnector.getInstance().getConnection();
			String sql = "SELECT * from " + CLASS_CONVERSATION_DB + "." + "class WHERE cid IN (SELECT class_cid FROM classrequest WHERE user_uid = ?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, uid);
			System.out.println("Get pending courses using "+uid);
			ResultSet result = ps.executeQuery();
			while (result.next()) {
				int cid = result.getInt("cid");
				String course_name = result.getString("cname");
				String course_description = result.getString("cdescription");
				Course c = new Course(cid, course_name, course_description);
				courses.add(c);
			}
			result.close();
			ps.close();
			conn.close();
		}
		catch (SQLException sqle) {
			sqle.printStackTrace();
		}
		return courses;
		
	}

	// Insert a new user information into the database
	public boolean insertUserInDB(User u){
		boolean insert = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String activation_code = null;
		try {
			conn = DBConnector.getInstance().getConnection();
			activation_code = UUID.randomUUID().toString();
			
			String query = "INSERT INTO " + CLASS_CONVERSATION_DB + "." + "user (uid, fname, lname, psuid, utype, activation_code) values(?, ?, ?, ?, ?, ?)";
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, u.getUid()); 
			pstmt.setString(2, u.getFName()); 
			pstmt.setString(3, u.getLName()); 
			pstmt.setString(4, u.getPSUID());
			pstmt.setInt(5, u.getUserType());
            pstmt.setString(6, activation_code);

			int result = pstmt.executeUpdate(); // execute insert statement
			if (result != 0){
				insert = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		sendValidationEmail(u, activation_code);
		return insert;
	}

    
    private void sendValidationEmail(User u, String activation_code) {
        Emailer emailer = new Emailer();
        String msg = "Dear " + u.getFullName() + ", <br>" +
                     "You have just created a profile on the Class Conversation System with your OpenId (" + u.getUid() + ") associated with your Penn State ID (" + u.getPSUID() + "). <br><br>" +
                     "Please click the following Link to activate your account on the Class Conversation System. <br><br>" +
                     "<a href=\"http://cscl.ist.psu.edu:5678/classconversation/service/LoginServlet?op=cm&u=" + u.getPSUID() + "&ac="+ activation_code + "\">" +
                     		"http://cscl.ist.psu.edu:5678/classconversation/service/LoginServlet?op=cm&u=" + u.getPSUID() + "&ac="+ activation_code + "</a>" +
                     		"<br><br> If you do not remember you have created this profile, please delete this message! <br><br>";
            
        emailer.sendEmail("DoNotReply@cscl.ist.psu.edu", u.getPSUID() + "@psu.edu", "Confirmation -- Class Converstation System registration Info", msg);        
    }

    public String getUserOpenId() {
        HttpServletRequest httpServletRequest = this.getThreadLocalRequest();
        HttpSession session = httpServletRequest.getSession();

        System.out.println("Get user open id here "+session.getAttribute(LoginServlet.USER_OPEN_ID));
        
        if (UserChecker.isUserActivated((String)session.getAttribute(LoginServlet.USER_OPEN_ID))) {
            return (String) session.getAttribute(LoginServlet.USER_OPEN_ID);
        }
        else {
            return (String) session.getAttribute(LoginServlet.USER_OPEN_ID) + ".NOTACTIVATED";
        }
    }
    
    public User loginFromSession(){
    	User user = getUserAlreadyFromSession();
    	System.out.println("Get the user from the session");
		return user;
    }
    
    private User getUserAlreadyFromSession(){
		User user = null;
		HttpServletRequest httpServletRequest = this.getThreadLocalRequest();
		HttpSession session = httpServletRequest.getSession();
		Object userObj = session.getAttribute(LoginServlet.CC_USER);
		if (userObj != null && userObj instanceof User) {
			user = (User) userObj;
		}
		return user;
	}

	public boolean markAsEnrolled(Course c, String uid) {
		boolean enrolled = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DBConnector.getInstance().getConnection();
			
			String query = "INSERT INTO " + CLASS_CONVERSATION_DB + "." + "userclass (user_uid, class_cid) values(?, ?)";
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, uid); 
			pstmt.setInt(2, c.getCourseID()); 

			int result = pstmt.executeUpdate(); // execute insert statement
			if (result != 0){
				enrolled = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		System.out.println("Enrolled "+ enrolled);
		return enrolled;
	}
    
    
    

}
